﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MAPDavy.Entities;
using System.Data.SqlClient;

namespace MAPDavy.Repository
{
    public class TermRepository:MasterRepository,ITermRepository
    {
        public List<Term> GetFromTermByAnio(string anio)
        {
            var query = from t in Context.Terms
                        .Include("MAP")
                        where t.Anio == anio
                        select t;

            return query.ToList(); 
        }

        public Term GetFromTermById(int id)
        {
            var query = from t in Context.Terms
                        .Include("MAP")
                        where t.Id == id
                        select t;

            return query.SingleOrDefault() ; 
        }

        public void AddTerm(Term term)
        {
            var ts = (from t in Context.Terms
                        where t.Anio == term.Anio
                        select t.Id).ToList() ;

            if (ts.Count  == 0)
            {
                var newId = term.Anio + 1;
                term.Id = Int32.Parse(newId.ToString());  
            }
            else
            {
                term.Id = ts.Max() + 1;
            }

            Context.Terms.Add(term);
            Context.SaveChanges();  
        }

        public void UpdateTerm(Term term)
        {
            var existe = (from t in Context.Terms
                        where t.Id == term.Id  
                        select t).SingleOrDefault();

            if (existe !=null)
            {
                existe.NameTerm = term.NameTerm;
                existe.Description = term.Description;
                //existe.FechaAplicacion = term.FechaAplicacion;
                existe.TestingStartDate = term.TestingStartDate;
                existe.TestingEndDate = term.TestingEndDate;
                existe.Cerrado = term.Cerrado;

                Context.Entry(existe).State = System.Data.EntityState.Modified;
                Context.SaveChanges();  
            }
        }

        public void RemoveTerm(Int32 id)
        {
            var existe = (from t in Context.Terms
                          where t.Id == id
                          select t).SingleOrDefault();

            if (existe != null)
            {
                Context.Terms.Remove(existe); 
                Context.SaveChanges();
            }
        }

        public bool CanRemoveTerm(int id)
        {
            var term = (from t in Context.Terms
                          .Include("TestGrades") 
                          where t.Id == id
                          select t).SingleOrDefault();
            
            if (term == null )
            {
                return false;
            }

            if (term.TestGrades.Count > 0 )
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public void AddTestGrade(TestGrade testGrade)
        {
            Context.TestGrades.Add(testGrade);
            Context.SaveChanges();
        }

        public void RemoveTestGrade(TestGrade testGrade)
        {
            var existe = (from tg in Context.TestGrades
                          where tg.TermId == testGrade.TermId &&
                                tg.GradeId == testGrade.GradeId &&
                                tg.TestId == testGrade.TestId
                          select tg).SingleOrDefault();

            if (existe != null)
            {
                Context.TestGrades.Remove(existe);  
                Context.SaveChanges();
            }
        }


        public bool CanRemoveTestGrade(TestGrade testGrade)
        {
            var existe = (from tg in Context.TestGrades
                          .Include("Scores")
                          where tg.TermId == testGrade.TermId &&
                                tg.GradeId == testGrade.GradeId &&
                                tg.TestId == testGrade.TestId
                          select tg).SingleOrDefault();

            if (existe == null)
            {
                return false;
            }

            if (existe.Scores.Count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        public List<CRFTemplate> GetFromCRFTemplateByTermId(int id)
        {
            var paramTermId = new SqlParameter("@termId", id);

            object[] param = new object[1];
            param[0] = paramTermId;

            var query = Context.Database.SqlQuery<CRFTemplate>("GetFromCRFTemplateByTermId @termId", param);

            return query.ToList();
        }
    }
}
